﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Settings;
using System.Data.OleDb;
using Utils;

namespace UtilConsole
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.ReadLine();
            string fileName = @"D:\Dropbox\Sami\Document\DanhsachCHvaTBHchoSAMI.xls";
            DataTable dtSource = new DataTable();
            using (OleDbConnection Oleconn = new OleDbConnection(string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", fileName)))
            {
                Oleconn.Open();
                using (OleDbCommand Olecmd = Oleconn.CreateCommand())
                {
                    Olecmd.CommandText = string.Format(@"SELECT DISTINCT * FROM [Dai ly$]");
                    dtSource.Load(Olecmd.ExecuteReader());
                    while (dtSource.Columns.Count > 3)
                        dtSource.Columns.RemoveAt(3);
                    dtSource.Columns[0].ColumnName = "AgencyName";
                    dtSource.Columns[1].ColumnName = "Address";
                    dtSource.Columns[2].ColumnName = "Email";
                    dtSource.Columns.Add("ParentID", typeof(int));
                    dtSource.Columns.Add("Ord", typeof(int));
                    int index = 2;
                    foreach (DataRow row in dtSource.Rows)
                    {
                        row["ParentID"] = 18;
                        row["Ord"] = index++;
                        row["Email"] = row["Email"].ToString().ToNormalPhoneNumber();
                    }
                }
                using (SqlBulkCopy bc = new SqlBulkCopy("SERVER = .; DATABASE = Panasonic_Warranty; UID = sa; PWD = sa123;", SqlBulkCopyOptions.FireTriggers))
                {
                    bc.BulkCopyTimeout = 0;
                    bc.DestinationTableName = "pa_Agency";
                    foreach (DataColumn col in dtSource.Columns)
                        bc.ColumnMappings.Add(col.ColumnName, col.ColumnName);
                    bc.WriteToServer(dtSource);
                }
            }
        }
    }
}
